//! @Author: DengLibin
//! @Date: Create in 2025-01-14 17:37:57
//! @Description: mysql dao

use chrono::{DateTime, NaiveDateTime, Utc};

use log::info;
use my_trait::{MustHaveFieldType, MustHaveTableName, Struct2Map};
use serde::Serialize;
use serde_json::Value;
use sqlx::{
    mysql::{MySqlArguments, MySqlPoolOptions, MySqlRow},
    FromRow, MySql, Pool, Transaction,
};
use sqlx::{Column, Row};

use crate::sys::global::{to_global_result, GlobalResult};

use super::super::PageData;

const DATE_TIME_PATTERN: &str = "%Y-%m-%d %H:%M:%S";

/// @Author: DengLibin
/// @Date: Create in 2024-07-16 14:01:51
/// @Description: 获取连接池
/// @param: url 连接地址 "mysql://root:password@localhost/todos"
pub async fn get_conn_pool(url: &str, max_connection: u32) -> GlobalResult<Pool<MySql>> {
    let r = MySqlPoolOptions::new()
        .max_connections(max_connection)
        .connect(url)
        .await;
    to_global_result(r)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-17 15:16:16
/// @Description: 获取查询的总数
pub async fn count_total(sql: &str, pool: &Pool<MySql>) -> GlobalResult<i64> {
    let r = sqlx::query_scalar(format!("SELECT COUNT(1) FROM ({})t", sql).as_str())
        .fetch_one(pool)
        .await;

    to_global_result(r)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-18 16:05:59
/// @Description: 根据id查询
pub async fn get_by_id<E>(id: i64, pool: &Pool<MySql>) -> GlobalResult<Option<E>>
where
    E: Send + Unpin + for<'r> FromRow<'r, MySqlRow> + MustHaveTableName,
{
    let sql = format!("SELECT * FROM {} where id=?", E::table_name());
    let x = sqlx::query_as::<MySql, E>(sql.as_str())
        .bind(id)
        .fetch_optional(pool)
        .await;
    to_global_result(x)
}

/// @Author: DengLibin
/// @Date: Create in 2025-03-21 10:34:24
/// @Description: 查询所有
pub async fn get_all<E>(pool: &Pool<MySql>) -> GlobalResult<Vec<E>>
where
    E: Serialize + Send + Unpin + for<'r> FromRow<'r, MySqlRow> + MustHaveTableName,
{
    let sql: String = format!("SELECT * FROM {}", E::table_name());
    let result = sqlx::query_as::<MySql, E>(&sql).fetch_all(pool).await?;
    Result::Ok(result)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-17 15:31:26
/// @Description: 查询分页数据
pub async fn query_page<E>(page: i32, size: i32, pool: &Pool<MySql>) -> GlobalResult<PageData<E>>
where
    E: Serialize + Send + Unpin + for<'r> FromRow<'r, MySqlRow> + MustHaveTableName,
{
    let offset = (page - 1) * size;

    let sql: String = format!("SELECT * FROM {}", E::table_name());
    info!("query_page:sql:{}", sql);
    let page_sql = format!("{} limit ? offset ?", sql);
    let e_future = sqlx::query_as::<MySql, E>(page_sql.as_str())
        .bind(size)
        .bind(offset)
        .fetch_all(pool);

    let total_future = count_total(sql.as_str(), pool);

    // 使用 tokio::join! 宏并行执行两个异步任务
    let (datas, total) = tokio::join!(e_future, total_future);

    let datas = to_global_result(datas)?;
    let total = to_global_result(total)?;
    Ok(PageData {
        page,
        size,
        total,
        data: datas,
    })
}

/// @Author: DengLibin
/// @Date: Create in 2025-03-19 16:43:44
/// @Description: 滚动查询所有
pub async fn scroll_all<E>(
    scroll_size: i64,
    pool: &Pool<MySql>,
    mut f: impl FnMut(Vec<E>) -> bool, //回调，返回true继续滚动，返回false 停止
) -> GlobalResult<()>
where
    E: Serialize + Send + Unpin + for<'r> FromRow<'r, MySqlRow> + MustHaveTableName + Struct2Map,
{
    let mut start_id = "0".to_string();
    loop {
        let sql: String = format!(
            "SELECT * FROM {} WHERE id>? order by id asc limit ?",
            E::table_name()
        );
        log::info!("sql:{}, start_id:{}", sql, start_id);

        let r = sqlx::query_as::<MySql, E>(&sql)
            .bind(&start_id)
            .bind(scroll_size)
            .fetch_all(pool)
            .await;
        let rows = to_global_result(r)?;
        // log::info!("结果数量:{}", rows.len());
        if rows.is_empty() {
            break;
        }

        if let Some(last_one) = rows.last() {
            let map = last_one.to_map();
            let last_id = map.get("id");
            if let Some(id) = last_id {
                if let Value::Number(v) = id {
                    start_id = v.to_string();
                } else if let Value::String(v) = id {
                    start_id = v.to_string();
                }
            }
        }
        //回调
        let b = f(rows);
        if !b {
            break;
        }
    }

    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2025-02-07 11:22:15
/// @Description: 查询数据
pub async fn query_sql<E>(sql: &str, pool: &Pool<MySql>) -> GlobalResult<Vec<E>>
where
    E: Serialize + Send + Unpin + for<'r> FromRow<'r, MySqlRow>,
{
    // 执行查询
    let rows = sqlx::query_as::<MySql, E>(sql).fetch_all(pool).await?;

    Ok(rows)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-18 16:42:06
/// @Description: 根据id删除
pub async fn delete_by_id<E>(id: i64, pool: &Pool<MySql>) -> GlobalResult<()>
where
    E: MustHaveTableName,
{
    let sql = format!("DELETE FROM {} WHERE id=?", E::table_name());
    let result = sqlx::query(&sql).bind(id).execute(pool).await;
    to_global_result(result)?;
    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 14:46:11
/// @Description: 根据id更新
pub async fn update_one<E>(id: Value, e: E, pool: &Pool<MySql>) -> GlobalResult<()>
where
    E: MustHaveTableName + MustHaveFieldType + Struct2Map,
{
    // 构建 SQL 查询

    let (fields, field_types, mut params) = super::super::field_type_vlues::<E>(&e);

    let fields_str = fields
        .iter()
        .map(|field| format!("{}=?", field))
        .collect::<Vec<String>>()
        .join(",");
    let sql = format!("update {} set {} where id=?", E::table_name(), fields_str);
    params.push(id);
    // 开启事务

    let mut tx: Transaction<'_, MySql> = to_global_result(pool.begin().await)?;
    // 执行 SQL 查询
    // 构建查询并绑定参数id
    let mut query: sqlx::query::Query<MySql, MySqlArguments> = sqlx::query::<MySql>(sql.as_str());

    //绑定更新参数
    query = bind_value(params, field_types, query)?;

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;
        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }

    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 16:08:51
/// @Description: 添加一个
pub async fn insert_one<E>(e: E, pool: &Pool<MySql>) -> GlobalResult<E>
where
    E: MustHaveTableName + MustHaveFieldType + Struct2Map,
{
    let (fields, types, params) = super::super::field_type_vlues(&e);
    // 构建 SQL 查询
    let sql_v: String = fields
        .iter()
        .map(|_| "?".to_string())
        .collect::<Vec<String>>()
        .join(",");
    let sql = format!(
        "INSERT INTO `{}`({}) VALUES({})",
        E::table_name(),
        fields.join(","),
        sql_v
    );

    log::info!("sql: {} \nparams: {:?}", sql, params);
    // 开启事务

    let mut tx: Transaction<'_, MySql> = to_global_result(pool.begin().await)?;
    // 执行 SQL 查询
    // 构建查询并绑定参数id
    let mut query: sqlx::query::Query<MySql, MySqlArguments> = sqlx::query::<MySql>(sql.as_str());

    //绑定更新参数
    query = bind_value(params, types, query)?;

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;
        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }

    Ok(e)
}

/// @Author: DengLibin
/// @Date: Create in 2025-02-07 11:34:11
/// @Description: 批量添加
/// @table_name: 表名
/// @colums: 字段
/// @rows: 数据
pub async fn insert_batch<E>(rows: &Vec<E>, pool: &Pool<MySql>) -> GlobalResult<()>
where
    E: MustHaveTableName + MustHaveFieldType + Struct2Map,
{
    if rows.is_empty() {
        return Ok(());
    }
    let mut tx = to_global_result(pool.begin().await)?;

    // 获取字段名和字段类型（取第一行即可）
    let (fields, types, _params) = super::super::field_type_vlues(&rows[0]);

    // 构造 values 占位符部分 (?, ?, ...), (?, ?, ...)
    let mut values_sql = vec![];
    let mut all_params = vec![];

    for row in rows {
        let params = super::super::field__vlues(row, &fields);
        let value_clause = format!("({})", vec!["?"; fields.len()].join(","));
        values_sql.push(value_clause);
        all_params.extend(params);
    }

    let sql = format!(
        "INSERT INTO {} ({}) VALUES {}",
        E::table_name(),
        fields.join(","),
        values_sql.join(",")
    );

    let mut query = sqlx::query::<MySql>(&sql);

    // 批量绑定所有参数
    let repeated_types: Vec<String> = (0..rows.len())
        .flat_map(|_| types.iter().cloned())
        .collect();
    query = bind_value(all_params, repeated_types, query)?;

    // 执行一次
    let result = query.execute(&mut *tx).await?;

    // for row in rows {
    //     let (fields, types, params) = super::super::field_type_vlues(row);
    //
    //     let mut values = vec![];
    //     for _i in 0..fields.len() {
    //         values.push(format!("?"));
    //     }
    //     let sql = format!(
    //         "insert into {}({})values ({})",
    //         E::table_name(),
    //         fields.join(","),
    //         values.join(",")
    //     );
    //
    //     let mut query = sqlx::query::<MySql>(&sql);
    //     //绑定更新参数
    //     query = bind_value(params, types, query)?;
    //
    //     let r = query.execute(&mut *tx).await;
    //     if let Err(e) = r {
    //         // 回滚事务
    //         to_global_result(tx.rollback().await)?;
    //         return Err(anyhow::anyhow!(e.to_string()));
    //     }
    // }

    //提交
    to_global_result(tx.commit().await)?;

    Ok(())
}

pub async fn exec_update_sql(sql: &str, pool: &Pool<MySql>) -> GlobalResult<()> {
    let _r = sqlx::query(&sql).execute(pool).await?;
    Ok(())
}

///@Author: DengLibin
///@Date: Create in 2025-05-09 13:49:02
///@Description: 查询数据
///@return 表头， 数据列表
pub async fn select_list(
    sql: &str,
    pool: &Pool<MySql>,
) -> GlobalResult<(Vec<String>, Vec<Vec<Value>>)> {
    let mut columns: Vec<String> = Vec::new();
    let mut row_list: Vec<Vec<Value>> = Vec::new();
    // 执行 SELECT  查询
    let rows = to_global_result(sqlx::query::<MySql>(sql).fetch_all(pool).await)?;
    for row in rows.iter() {
        let mut row_data: Vec<Value> = Vec::new();
        //字段
        let row_columns = row.columns();
        if columns.is_empty() {
            for row_column in row_columns {
                columns.push(row_column.name().to_string());
            }
        }
        //当前行数据
        for index in 0..row_columns.len() {
            let v: Value = row
                .try_get::<i64, _>(index)
                .map(Value::from)
                .or_else(|_| row.try_get::<i64, _>(index).map(Value::from)) //数字类型
                .or_else(|_| row.try_get::<bool, _>(index).map(Value::from)) //bool类型
                .or_else(|_| row.try_get::<String, _>(index).map(Value::from)) //字符串类型
                .or_else(|_| {
                    row.try_get::<NaiveDateTime, _>(index).map(|time| {
                        //时间类型
                        return Value::from(time.format(DATE_TIME_PATTERN).to_string());
                    })
                })
                .unwrap_or(Value::Null);
            row_data.push(v);
        }
        row_list.push(row_data);
    }

    Ok((columns, row_list))
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 14:40:00
/// @Description: 绑定参数
fn bind_value<'q>(
    params: Vec<Value>, //参数
    types: Vec<String>, //参数类型(和参数一一对应)
    mut query: sqlx::query::Query<'q, MySql, MySqlArguments>,
) -> GlobalResult<sqlx::query::Query<'q, MySql, MySqlArguments>> {
    let mut index = 0;
    //绑定值
    for value in params {
        let type_s = types[index].as_str();
        match value {
            Value::Null => match type_s {
                "Option<DateTime<Utc>>" => query = query.bind::<Option<DateTime<Utc>>>(None),
                "Option<NaiveDateTime>" => query = query.bind::<Option<DateTime<Utc>>>(None),
                "Option<i64>" => query = query.bind::<Option<i64>>(None),
                "Option<i32>" => query = query.bind::<Option<i32>>(None),
                "Option<f64>" => query = query.bind::<Option<f64>>(None),
                "Option<f32>" => query = query.bind::<Option<f32>>(None),
                "Option<String>" => query = query.bind::<Option<String>>(None),

                _ => {
                    return Err(anyhow::anyhow!(format!("Null类型错误:{}", type_s)));
                }
            },
            Value::Bool(b) => query = query.bind::<bool>(b),
            Value::Number(n) => match type_s {
                "Option<i64>" => query = query.bind::<Option<i64>>(Some(n.as_i64().unwrap())),
                "Option<i32>" => {
                    query = query.bind::<Option<i32>>(Some(n.as_i64().unwrap() as i32))
                }
                "Option<f64>" => query = query.bind::<Option<f64>>(Some(n.as_f64().unwrap())),
                "Option<f32>" => {
                    query = query.bind::<Option<f32>>(Some(n.as_f64().unwrap() as f32))
                }
                "i64" => query = query.bind::<i64>(n.as_i64().unwrap()),
                "i32" => query = query.bind::<i32>(n.as_i64().unwrap() as i32),
                "f64" => query = query.bind::<f64>(n.as_f64().unwrap()),
                "f32" => query = query.bind::<f32>(n.as_i64().unwrap() as f32),
                _ => {
                    return Err(anyhow::anyhow!(format!("Number类型错误:{}", type_s)));
                }
            },
            Value::String(s) => match type_s {
                "String" => query = query.bind::<String>(s),
                "Option<String>" => query = query.bind::<Option<String>>(Some(s)),
                "DateTime<Utc>" => {
                    let date_r = s.parse::<DateTime<Utc>>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<DateTime<Utc>>(date);
                }
                "Option<DateTime<Utc>>" => {
                    let date_r = s.parse::<DateTime<Utc>>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<Option<DateTime<Utc>>>(Some(date));
                }
                "NaiveDateTime" => {
                    let date_r = s.parse::<NaiveDateTime>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<NaiveDateTime>(date);
                }
                "Option<NaiveDateTime>" => {
                    let date_r = s.parse::<NaiveDateTime>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<Option<NaiveDateTime>>(Some(date));
                }

                _ => {
                    return Err(anyhow::anyhow!(format!("String类型错误:{}", type_s)));
                }
            },
            _ => {}
        }
        index += 1;
    }
    Ok(query)
}
